package com.zhangyue.programmer.dao;

import com.zhangyue.programmer.bean.User;
import com.zhangyue.programmer.dto.UserDto;
import com.zhangyue.programmer.util.DBConn;
import com.zhangyue.programmer.util.ResultSetUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

public class UserDao {
    public User getUserById(int id) throws Exception {
        User user = null;
        Connection conn = DBConn.getConn();
        String sql = "select * from t_user where id=?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setObject(1,id);
        ResultSet rs = ps.executeQuery();
        if(rs != null){
            List<User> list = ResultSetUtil.getList(rs, User.class);
            if(list.size()>0){
                user = list.get(0);
            }
        }
        return user;
    }

    public List<User> getUserAllList() throws Exception {
        Connection conn = DBConn.getConn();
        String sql = "select * from t_user";
        PreparedStatement ps = conn.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
        if(rs != null){
            return ResultSetUtil.getList(rs, User.class);
        }
        return null;
    }

    public List<User> getUserByPaged(int page,int size) throws Exception {
        Connection conn = DBConn.getConn();
        String sql = "select * from t_user limit "+ size +" offset "+(page-1)*size;
        PreparedStatement ps = conn.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
        if(rs != null){
            return ResultSetUtil.getList(rs, User.class);
        }
        return null;
    }

    public int getTotalSize() throws Exception {
        Connection conn = DBConn.getConn();
        String sql = "select count(1) from t_user";
        PreparedStatement ps = conn.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
        int total = 0;
        if(rs != null && rs.next()){
            total= rs.getInt(1);
        }
        DBConn.close(ps,rs);
        return total;
    }

    public boolean add(UserDto dto) throws SQLException {
        Connection conn = DBConn.getConn();
        String sql = "insert into t_user values (0,?,?,?)";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setObject(1,dto.getName());
        ps.setObject(2,dto.getAge());
        ps.setObject(3,dto.getRole());
        int num = ps.executeUpdate();
        DBConn.close(ps,null);
        return num>0;
    }

    public boolean delete(UserDto dto) throws SQLException {
        Connection conn = DBConn.getConn();
        String sql = "delete from t_user where id=?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setObject(1,dto.getId());
        int num = ps.executeUpdate();
        DBConn.close(ps,null);
        return num>0;

    }

    public Object update(UserDto dto) throws SQLException {
        Connection conn = DBConn.getConn();
        String sql = "update t_user set name=?,age=?,role=? where id=?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setObject(1,dto.getName());
        ps.setObject(2,dto.getAge());
        ps.setObject(3,dto.getRole());
        ps.setObject(4,dto.getId());
        int num = ps.executeUpdate();
        DBConn.close(ps,null);
        return num>0;
    }
}
